package com.xframe.xdal.core.manage;

import cn.hutool.core.util.ObjectUtil;
import com.xframe.xdal.core.XDataSource;
import com.xframe.xdal.core.constant.DataSourceTypeEnums;
import com.xframe.xdal.core.constant.ErrorCode;
import com.xframe.xdal.core.convert.DBConvertInfo;
import com.xframe.xdal.core.convert.IDBTypeStrategy;
import com.xframe.xdal.core.convert.OracleDBHelper;
import com.xframe.xdal.core.exception.DalFrameException;
import com.xframe.xdal.core.model.mapping.*;
import com.xframe.xdal.core.util.CollectionUtil;
import com.xframe.xdal.core.util.StringUtil;
import lombok.extern.log4j.Log4j2;

import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

@Log4j2
public class OracleDBManage extends BaseDbManage {


    private final String queryTables = " SELECT OBJECT_NAME AS \"tableName\", OBJECT_ID   AS \"tableID\", OBJECT_TYPE as \"tableType\", COMMENTS    AS \"tableDesc\" FROM DBA_OBJECTS DOS LEFT JOIN USER_TAB_COMMENTS UTCS ON DOS.OBJECT_NAME = UTCS.TABLE_NAME WHERE (OBJECT_TYPE = 'VIEW' OR OBJECT_TYPE = 'TABLE' ) AND DOS.OWNER ='{}' ";
    private final String queryTablesWithTableName = queryTables + " and OBJECT_NAME='{}'";
    private final String queryTableColumns = "SELECT T.column_name AS \"columnName\",\n" +
            " T.column_type AS \"dataType\",T.data_length AS \"dataLength\",\n" +
            " T.data_Precision AS \"dataPrecision\", T.data_scale AS \"dataScale\",\n" +
            " T.TABLE_NAME AS \"tableName\",  T.column_comment AS \"columnDesc\",\n" +
            " T.column_id AS \"orderNo\",  case when instr(b.constraint_type,'P')>0  then  1 else 0 end as \"isPk\",\n" +
            " case when instr(b.constraint_type,'U')>0   then 1 else  0 end as \"isUnique\",\n" +
            " DEcode(T.nullable, 'N', 0, 1) \"nullable\"\n" +
            " FROM (SELECT ub.owner as OWNER, UTC.table_name     AS table_name,UTC.column_name    AS column_name,\n" +
            " UTC.data_length    AS data_length,  UTC.data_type      AS column_type, utc.data_Precision as data_Precision,\n" +
            " utc.data_scale     AS data_scale, ucc.comments       AS column_comment,utc.column_id as column_id,\n" +
            " utc.nullable FROM dba_objects ub LEFT JOIN user_tab_columns utc  ON ub.OBJECT_NAME = UTC.table_name\n" +
            " LEFT JOIN user_col_comments ucc ON utc.column_name = ucc.column_name AND utc.table_name = ucc.table_name WHERE (ub.OBJECT_TYPE = 'VIEW' OR ub.OBJECT_TYPE = 'TABLE' ) AND  ub.OWNER = '{}' ) T\n" +
            " LEFT JOIN (SELECT UCC.table_name AS table_name,ucc.column_name AS column_name, wm_concat(UC.constraint_type) AS constraint_type\n" +
            " FROM user_cons_columns ucc LEFT JOIN user_constraints uc ON UCC.constraint_name = UC.constraint_name\n" +
            " GROUP BY UCC.table_name, ucc.column_name) b ON T.table_name = b.TABLE_NAME  AND T.column_name = b.column_name";

    IDBTypeStrategy dbTypeHelper;
    private final String queryTableColumnsWithTableName = "SELECT T.column_name AS \"columnName\",\n" +
            " T.column_type AS \"dataType\",T.data_length AS \"dataLength\",\n" +
            " T.data_Precision AS \"dataPrecision\", T.data_scale AS \"dataScale\",\n" +
            " T.TABLE_NAME AS \"tableName\",  T.column_comment AS \"columnDesc\",\n" +
            " T.column_id AS \"orderNo\",  case when instr(b.constraint_type,'P')>0  then  1 else 0 end as \"isPk\",\n" +
            " case when instr(b.constraint_type,'U')>0   then 1 else  0 end as \"isUnique\",\n" +
            " DEcode(T.nullable, 'N', 0, 1) \"nullable\"\n" +
            " FROM (SELECT ub.owner as OWNER, UTC.table_name     AS table_name,UTC.column_name    AS column_name,\n" +
            " UTC.data_length    AS data_length,  UTC.data_type      AS column_type, utc.data_Precision as data_Precision,\n" +
            " utc.data_scale     AS data_scale, ucc.comments       AS column_comment,utc.column_id as column_id,\n" +
            " utc.nullable FROM dba_objects ub LEFT JOIN user_tab_columns utc  ON ub.OBJECT_NAME = UTC.table_name\n" +
            " LEFT JOIN user_col_comments ucc ON utc.column_name = ucc.column_name AND utc.table_name = ucc.table_name WHERE (ub.OBJECT_TYPE = 'VIEW' OR ub.OBJECT_TYPE = 'TABLE' ) AND  ub.OWNER = '{}' and ub.OBJECT_NAME='{}') T\n" +
            " LEFT JOIN (SELECT UCC.table_name AS table_name,ucc.column_name AS column_name, wm_concat(UC.constraint_type) AS constraint_type\n" +
            " FROM user_cons_columns ucc LEFT JOIN user_constraints uc ON UCC.constraint_name = UC.constraint_name\n" +
            " GROUP BY UCC.table_name, ucc.column_name) b ON T.table_name = b.TABLE_NAME  AND T.column_name = b.column_name";
//    private final String dropTableSqlTemplate = " DROP TABLE  {}; ";
//    private final String tableStructTemplate = " CREATE TABLE {} ";
//    private final String tableColumnTemplate = " {} {} {} COMMENT '{}'{} ";
//    private final String primaryKeyTemplate = " PRIMARY KEY (`{}`) ";
//    private final String UniqueIndexTemplate = " CREATE UNIQUE INDEX AK_{}_{} ON {} ({}); ";

    INameConvert nameConvert;

    public OracleDBManage(XDataSource dataSource) {
        super(dataSource);
        dbTypeHelper = new OracleDBHelper();
        nameConvert = NameConvertFactory.create(DataSourceTypeEnums.Oracle);
    }

    /**
     * 获取当前用户下所有的数据库表定义
     *
     * @return
     */
    @Override
    public List<DBTableInfo> getTableInfoList() {
        try {
            List<DBTableInfo> tableInfoList = null;

            tableInfoList = dataSource.executeQuery(StringUtil.format(this.queryTables,
                    dataSource.getUsername().toUpperCase()), null, ResultHandler.create(DBTableInfo.class));

            if (CollectionUtil.isEmpty(tableInfoList)) {
                return null;
            }
            List<DBColumnInfo> tableColumnInfoList = this.getDbColumnInfoList(null);
            List<DBColumnInfo> taleColumnInfo;
            for (DBTableInfo tableInfo : tableInfoList) {
                taleColumnInfo = tableColumnInfoList.stream().
                        filter(item -> (item != null && StringUtil.isNotEmpty(item.getTableName())
                                && item.getTableName().equals(tableInfo.getTableName())))
                        .collect(Collectors.toList());
                if (!CollectionUtil.isEmpty(taleColumnInfo))
                    tableInfo.setDbColumnInfoList(taleColumnInfo);
            }
            return tableInfoList;
        }catch (SQLException sqlException){
            throw new DalFrameException(ErrorCode.SQL_ERROR,sqlException.getMessage());
        }

    }

    /**
     * 根据制定表明获取
     *
     * @param tableName
     * @return
     */
    @Override
    public List<DBColumnInfo> getDbColumnInfoList(String tableName) {
        try {
            String tempTableName = queryTableColumns;
            if (!StringUtil.isEmpty(tableName)) {
                tempTableName = queryTableColumnsWithTableName;
                tableName = nameConvert.toName(tableName);
            }
            List<DBColumnInfo> dbColumnInfoList = dataSource.executeQuery(StringUtil.format(tempTableName,
                            dataSource.getUsername().toUpperCase(), tableName),
                    null, ResultHandler.create(DBColumnInfo.class));
            return dbColumnInfoList;
        }catch (SQLException sqlException){
            throw new DalFrameException(ErrorCode.SQL_ERROR,sqlException.getMessage());
        }
    }

    @Override
    public Map<String, DBTableInfo> getTableInfoMap(TableKeyMode tableKeyMode) {
        Map<String, DBTableInfo> rtnMapInfo = new HashMap<>();
        List<DBTableInfo> tableInfoList = this.getTableInfoList();
        if (tableInfoList.isEmpty()) {
            return rtnMapInfo;
        }
        if(TableKeyMode.KEY == tableKeyMode){
            tableInfoList.forEach(item -> rtnMapInfo.put(item.getTableID(), item));
        }else {
            tableInfoList.forEach(item -> rtnMapInfo.put(item.getTableName(), item));
        }
        return rtnMapInfo;
    }

    /**
     * @param tableName  表名
     * @param isRecreate
     * @return
     */
    @Override
    public DBTableInfo getTableInfo(String tableName, boolean isRecreate) {
        try {
            if (StringUtil.isEmpty(tableName)) {
                return null;
            }
            List<DBTableInfo> tableInfoList = dataSource.executeQuery(StringUtil.format(this.queryTablesWithTableName,
                    dataSource.getUsername().toUpperCase(), tableName.toUpperCase()), null, ResultHandler.create(DBTableInfo.class));
            if (CollectionUtil.isEmpty(tableInfoList)) {
                return null;
            }
            DBTableInfo tableInfo = tableInfoList.get(0);
            if (isRecreate) {
                List<DBColumnInfo> tableColumnInfoList = this.getDbColumnInfoList(tableName);
                List<DBColumnInfo> taleColumnInfo = tableColumnInfoList.stream().filter(item ->
                        (item != null && StringUtil.isNotEmpty(item.getTableName())
                                && item.getTableName().equals(tableInfo.getTableName()))).collect(Collectors.toList());
                if (!CollectionUtil.isEmpty(taleColumnInfo)) {
                    tableInfo.setDbColumnInfoList(taleColumnInfo);
                }
            }
            return tableInfo;
        }catch (SQLException sqlException){
            throw new DalFrameException(ErrorCode.SQL_ERROR,sqlException.getMessage());
        }
    }

    /**
     * 生成表结构
     *
     * @param tableInfo  ITableInfo 对象
     * @param isRecreate 是否重新生成表结构
     * @return
     */
    @Override
    public boolean createTable(ITableInfo tableInfo, boolean isRecreate) throws Exception {
        if (tableInfo == null) {
            return false;
        }
        String tableName = nameConvert.toName(tableInfo.getName());
        if (StringUtil.isEmpty(tableName)) {
            return false;
        }


        List<IColumnMapping> columnMappingList = tableInfo.getColumnMappingList();
        if (CollectionUtil.isEmpty(columnMappingList)) {
            throw new Exception(StringUtil.format("The table {} not define column info", tableName));
        }
        IColumnMapping pkColumnMapping = tableInfo.findPkFirst();
        if (pkColumnMapping == null) {
            throw new Exception(StringUtil.format("The table {} not define Primary Key", tableName));
        }
        StringBuilder sb = new StringBuilder();
        StringBuilder dropTable = new StringBuilder();
        if (isRecreate) {
            dropTable.append("DROP TABLE ").append(tableName);
        }
        sb.append("CREATE TABLE ").append(tableName).append("(");
        //拼接列
        String space = " ";
        StringBuilder pkSqlBuilder = new StringBuilder(" CONSTRAINT PK_").append(tableName).append(" PRIMARY KEY ").append("(");
        //  StringBuilder ueSqlBuilderAll = new StringBuilder();
        List<String> ukeys = new ArrayList<>();
        StringBuilder ueSqlBuilder = new StringBuilder("ALTER TABLE ").append(tableName).append(" ADD CONSTRAINT AK_")/*.append(tableName).append("_")*/;
        //StringBuilder commentBuilder = new StringBuilder();
        List<String> commentList = new ArrayList<>();
        StringBuilder commentBuilderOne = new StringBuilder("COMMENT ON COLUMN ").append(tableName).append(".");
        int commentBuilderLength = commentBuilderOne.length();
        int length = ueSqlBuilder.length();
        for (int i = 0; i < columnMappingList.size(); i++) {
            IColumnMapping iColumnMapping = columnMappingList.get(i);
            DBConvertInfo dbConvertInfo = new DBConvertInfo(iColumnMapping.getDataType(), iColumnMapping.getLength(), iColumnMapping.getScale());
            String fieldName = nameConvert.toName(iColumnMapping.getName());
            sb.append(space).append(fieldName).append(space).append(dbTypeHelper.getDbType(dbConvertInfo));
            if (!iColumnMapping.getNullFlag()) {
                sb.append(" NOT NULL ");
            }
            sb.append(",");
            String desc = iColumnMapping.getDesc();
            if (StringUtil.isNotEmpty(desc)) {
                commentBuilderOne.append(fieldName).append(" is '").append(desc).append("'");
                //commentBuilder.append(commentBuilderOne.toString());
                commentList.add(commentBuilderOne.toString());
                commentBuilderOne.setLength(commentBuilderLength);
            }
            boolean primaryKeyFlag = iColumnMapping.getPrimaryKeyFlag();
            if (primaryKeyFlag) {
                pkSqlBuilder.append(fieldName).append(",");
            }
            boolean uniqueFlag = iColumnMapping.getUniqueFlag();
            if (uniqueFlag) {
                // ueSqlBuilderAll.append(ueSqlBuilder.append(fieldName).append(" UNIQUE (").append(fieldName).append(");").toString());
                ukeys.add(ueSqlBuilder.append(fieldName).append(" UNIQUE (").append(fieldName).append(")").toString());
                ueSqlBuilder.setLength(length);
            }
        }

        pkSqlBuilder.deleteCharAt(pkSqlBuilder.length() - 1);
        pkSqlBuilder.append("))");
        sb.append(pkSqlBuilder);
       /* if (isRecreate) {
            dataSource.execute(dropTable.toString());
        }*/
        dataSource.execute(sb.toString());
        if (!CollectionUtil.isEmpty(commentList)) {
            for (Iterator<String> iterator = commentList.iterator(); iterator.hasNext(); ) {
                String next = iterator.next();
                dataSource.execute(next);
            }
        }
        if (!CollectionUtil.isEmpty(ukeys)) {
            for (Iterator<String> iterator = ukeys.iterator(); iterator.hasNext(); ) {
                String next = iterator.next();
                dataSource.execute(next);
            }
        }
        if (!StringUtil.isEmpty(tableInfo.getLabel())) {
            dataSource.execute(StringUtil.format("COMMENT ON TABLE {} IS '{}'", tableName, tableInfo.getLabel()));
        }
        //sb.append(ueSqlBuilderAll);
        //sb.append(commentBuilder);

        return true;
    }


    /**
     * 删除物理表
     * @param tableInfo
     * @return
     * @throws Exception
     */
    @Override
    public boolean deleteTable(ITableInfo tableInfo) throws Exception {
        if (tableInfo == null) {
            return false;
        }
        String tableName = nameConvert.toName(tableInfo.getName());
        if (StringUtil.isEmpty(tableName)) {
            return false;
        }
        StringBuilder dropTable = new StringBuilder();
        dropTable.append("DROP TABLE ").append(tableName);
        dataSource.execute(dropTable.toString());
        return true;
    }






    /**
     * 生成历史表结构
     * 生成历史表规则
     * 1.历史表名添加HIS_前缀
     * 2.历史表将原始表主键添加前缀HIS_，并新建新的主键
     *
     * @param tableInfo ITableInfo 需要生成历史表的对象
     * @return
     */
    @Override
    public boolean createHisTable(ITableInfo tableInfo) throws Exception {
        String tablePrefix = "HIS_";
        String columnPrefix = "HIS_";
        if (ObjectUtil.isEmpty(tableInfo)) {
            return false;
        }

        String hisTableName = tablePrefix + nameConvert.toName(tableInfo.getName());
        //判断当前原表名对应的历史表名的表是否存在
        DBTableInfo dbTableInfo = this.getTableInfo(hisTableName, false);
        String tableNameStr = hisTableName + "_{}";
        if (dbTableInfo != null) {
            int i = 1;
            while (true) {
                String tableNameNew = StringUtil.format(tableNameStr, i);
                DBTableInfo dbTableInfoLoop = this.getTableInfo(tableNameNew, false);
                if (dbTableInfoLoop == null) {
                    hisTableName = tableNameNew;
                    break;
                }
                i++;
            }
        }

        ITableInfo hisTableInfo = this.packHisTableInfo(tableInfo, hisTableName);
        return this.createTable(hisTableInfo, false);

    }
}
